﻿using MySql.Data.MySqlClient;
using NLog;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;

namespace ResourceTypeConfigurationApp
{
    public static class MySQLHelper
    {
        private static readonly Logger Logger = LogManager.GetCurrentClassLogger();
        public static readonly string targetConnectionString;
        public static readonly string soureConnectionString;

        // 修改静态构造函数，移除访问修饰符
        static MySQLHelper()
        {
            targetConnectionString = ConfigurationManager.ConnectionStrings["connectString"].ConnectionString;
            soureConnectionString = ConfigurationManager.ConnectionStrings["connectStringSource"].ConnectionString;
        }


        public static async Task<DbDataReader> ExecuteReaderAsync(string commandText)
        {

            try
            {
                using (var connection = new MySqlConnection(targetConnectionString))
                {
                    await connection.OpenAsync();
                    using (var command = new MySqlCommand(commandText, connection))
                    {
                        DbDataReader reader = await command.ExecuteReaderAsync();

                        return reader;
                    }
                }
            }
            catch (Exception ex)
            {
                Logger.Error(ex, $"执行 SQL 查询时出错: {commandText}");
                throw;
            }
        }

        /// <summary>
        /// 执行 SQL 查询并返回 DataTable
        /// </summary>
        /// <param name="sql">SQL 查询语句</param>
        /// <param name="parameters">SQL 参数（可选）</param>
        /// <param name="transaction">数据库事务（可选）</param>
        /// <returns>包含查询结果的 DataTable</returns>
        public static async Task<DataTable> GetDataTableAsync(
            string sql,
            Dictionary<string, object> parameters = null,
            MySqlTransaction transaction = null)
        {
            if (string.IsNullOrWhiteSpace(sql))
            {
                throw new ArgumentNullException(nameof(sql), "SQL 查询语句不能为空");
            }

            var dataTable = new DataTable();

            // 确定使用的连接和事务
            var connection = transaction?.Connection ?? new MySqlConnection(
                transaction != null ? transaction.Connection.ConnectionString : targetConnectionString);

            bool isExternalConnection = transaction != null || connection.State == ConnectionState.Open;

            try
            {
                if (!isExternalConnection)
                {
                    await connection.OpenAsync();
                }

                using (var command = new MySqlCommand(sql, connection, transaction))
                {
                    // 添加参数
                    if (parameters != null)
                    {
                        foreach (var param in parameters)
                        {
                            command.Parameters.AddWithValue(param.Key, param.Value ?? DBNull.Value);
                        }
                    }

                    // 执行查询并填充 DataTable
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        dataTable.Load(reader);
                    }

                    return dataTable;
                }
            }
            catch (Exception ex)
            {
                Logger.Error(ex, $"执行 SQL 查询时出错: {sql}");
                throw;
            }
            finally
            {
                // 关闭内部创建的连接
                if (!isExternalConnection && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
        }

        /// <summary>
        /// 执行 SQL 查询并返回 DataTable(查询数据源数据库)
        /// </summary>
        /// <param name="sql">SQL 查询语句</param>
        /// <param name="parameters">SQL 参数（可选）</param>
        /// <param name="transaction">数据库事务（可选）</param>
        /// <returns>包含查询结果的 DataTable</returns>
        public static async Task<DataTable> GetDataTableAsyncForSource(
            string sql,
            Dictionary<string, object> parameters = null,
            MySqlTransaction transaction = null)
        {
            if (string.IsNullOrWhiteSpace(sql))
            {
                throw new ArgumentNullException(nameof(sql), "SQL 查询语句不能为空");
            }

            var dataTable = new DataTable();

            // 确定使用的连接和事务
            var connection = transaction?.Connection ?? new MySqlConnection(
                transaction != null ? transaction.Connection.ConnectionString : soureConnectionString);

            bool isExternalConnection = transaction != null || connection.State == ConnectionState.Open;

            try
            {
                if (!isExternalConnection)
                {
                    await connection.OpenAsync();
                }

                using (var command = new MySqlCommand(sql, connection, transaction))
                {
                    // 添加参数
                    if (parameters != null)
                    {
                        foreach (var param in parameters)
                        {
                            command.Parameters.AddWithValue(param.Key, param.Value ?? DBNull.Value);
                        }
                    }

                    // 执行查询并填充 DataTable
                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        dataTable.Load(reader);
                    }

                    return dataTable;
                }
            }
            catch (Exception ex)
            {
                Logger.Error(ex, $"执行 SQL 查询时出错: {sql}");
                throw;
            }
            finally
            {
                // 关闭内部创建的连接
                if (!isExternalConnection && connection.State != ConnectionState.Closed)
                {
                    connection.Close();
                }
            }
        }





        /// <summary>
        /// 是否存在
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="par">参数</param>
        /// <param name="conntion">数据库</param>
        /// <returns>BOOLEAN</returns>
        public static bool Exists(string conntion, string sql, List<MySqlParameter> par = null)
        {
            DataSet dataSet = null;
            try
            {
                dataSet = GetDataSetBySql(conntion, sql, par);
            }
            catch (Exception ex)
            {
                Logger.Error(ex, $"执行 SQL 语句时出错: {sql}");
                return false;
            }
            DataTable dt = dataSet.Tables[0];
            if (dt.Rows.Count == 0 || dt.Rows[0][0].ToString() == "0")
            {
                return false;
            }
            return true;
        }

        public static async Task<bool> ExecuteSql(string conntion, string m_sqlStr, MySqlParameter[] parameters = null)
        {
            using (MySqlConnection connection = new MySqlConnection(conntion))
            {
                using (MySqlCommand cmd = new MySqlCommand(m_sqlStr, connection))
                {
                    cmd.CommandType = CommandType.Text;
                    if (parameters != null)
                    {
                        foreach (MySqlParameter parameter in parameters)
                        {
                            cmd.Parameters.Add(parameter);
                        }
                    }
                    try
                    {
                        connection.Open();
                        if (await cmd.ExecuteNonQueryAsync() > 0)
                        {
                            return true;
                        }
                        return false;
                    }
                    catch (Exception ex)
                    {
                        Logger.Error(ex, $"执行 SQL 语句时出错: {m_sqlStr}");
                        return false;
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
        }

        public static DataTable GetDataTableBySql(string conntion, string m_sqlStr, List<MySqlParameter> parameters = null)
        {
            DataSet dataSet = null;
            try
            {
                dataSet = GetDataSetBySql(conntion, m_sqlStr, parameters);
            }
            catch (Exception ex)
            {
                Logger.Error(ex, $"执行 SQL 语句时出错: {m_sqlStr}");
                return null;
            }
            return dataSet.Tables[0];
        }

        public static DataSet GetDataSetBySql(string conntion, string m_sqlStr, List<MySqlParameter> parameters = null)
        {
            DataSet ds = new DataSet();

            using (MySqlConnection connection = new MySqlConnection(conntion))
            {
                using (MySqlCommand cmd = new MySqlCommand(m_sqlStr, connection))
                {
                    cmd.CommandType = CommandType.Text;
                    if (parameters != null)
                    {
                        foreach (MySqlParameter parameter in parameters)
                        {
                            cmd.Parameters.Add(parameter);
                        }
                    }
                    try
                    {
                        connection.Open();
                        new MySqlDataAdapter(cmd).Fill(ds);
                        return ds;
                    }
                    catch (Exception ex)
                    {
                        Logger.Error(ex, $"执行 SQL 语句时出错: {m_sqlStr}");
                        throw;
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
        }

        public async static Task<DataTable> GetDataTableBySqlAsync(string conntion, string m_sqlStr, List<MySqlParameter> parameters = null)
        {
            DataSet dataSet = null;
            try
            {
                dataSet = await GetDataSetBySqlAsync(conntion, m_sqlStr, parameters);
            }
            catch (Exception ex)
            {
                Logger.Error(ex, $"执行 SQL 语句时出错: {m_sqlStr}");
                return null;
            }
            return dataSet.Tables[0];
        }


        public static async Task<DataSet> GetDataSetBySqlAsync(string conntion, string m_sqlStr, List<MySqlParameter> parameters = null)
        {
            DataSet ds = new DataSet();

            using (MySqlConnection connection = new MySqlConnection(conntion))
            {
                using (MySqlCommand cmd = new MySqlCommand(m_sqlStr, connection))
                {
                    cmd.CommandType = CommandType.Text;
                    if (parameters != null)
                    {
                        foreach (MySqlParameter parameter in parameters)
                        {
                            cmd.Parameters.Add(parameter);
                        }
                    }
                    try
                    {
                        connection.Open();
                        MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
                        await adapter.FillAsync(ds);
                        return ds;
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
        }

        public static object RunSqlScalar(string conntion, string m_sqlStr, MySqlParameter[] parameters = null)
        {
            using (MySqlConnection connection = new MySqlConnection(conntion))
            {
                using (MySqlCommand cmd = new MySqlCommand(m_sqlStr, connection))
                {
                    cmd.CommandType = CommandType.Text;
                    if (parameters != null)
                    {
                        foreach (MySqlParameter parameter in parameters)
                        {
                            cmd.Parameters.Add(parameter);
                        }
                    }
                    try
                    {
                        connection.Open();
                        return cmd.ExecuteScalar();
                    }
                    finally
                    {
                        connection.Close();
                    }
                }
            }
        }

        public async static Task<bool> ExecuteSqlAsync(string conntion, List<string> SQLStringList, List<MySqlParameter[]> parametersList = null)
        {
            using (MySqlConnection connection = new MySqlConnection(conntion))
            {
                connection.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = connection;
                MySqlTransaction tx = connection.BeginTransaction();
                cmd.Transaction = tx;
                string m_sqlStr = "";
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.Parameters.Clear();
                            if (parametersList != null)
                            {
                                foreach (MySqlParameter parameter in parametersList[n])
                                {
                                    cmd.Parameters.Add(parameter);
                                }
                            }
                            m_sqlStr = strsql;
                            cmd.CommandText = strsql;
                            await cmd.ExecuteNonQueryAsync();
                        }
                    }
                    tx.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    tx.Rollback();
                    Logger.Error(ex, $"执行 SQL 语句时出错: {m_sqlStr}");
                    return false;
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        public async static Task<bool> ExecuteSqlAsync(string conntion1, List<string> SQLStringList1
            , string conntion2, List<string> SQLStringList2
            , List<MySqlParameter[]> parametersList1 = null, List<MySqlParameter[]> parametersList2 = null)
        {
            using (MySqlConnection connection = new MySqlConnection(conntion1))
            {
                connection.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = connection;
                MySqlTransaction tx = connection.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < SQLStringList1.Count; n++)
                    {
                        string strsql = SQLStringList1[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.Parameters.Clear();
                            if (parametersList1 != null)
                            {
                                foreach (MySqlParameter parameter in parametersList1[n])
                                {
                                    cmd.Parameters.Add(parameter);
                                }
                            }
                            cmd.CommandText = strsql;
                            await cmd.ExecuteNonQueryAsync();
                        }
                    }
                    bool r = await ExecuteSqlAsync(conntion2, SQLStringList2, parametersList2);
                    if (r)
                    {
                        tx.Commit();
                        return true;
                    }
                    else
                    {
                        tx.Rollback();
                        return false;
                    }
                }
                catch (Exception e)
                {
                    tx.Rollback();
                    throw e;
                }
                finally
                {
                    connection.Close();
                }
            }
        }




        //插入数据
        public static async Task BulkInsertAsync(List<string> sqlCommands, List<Dictionary<string, object>> dataList = null)
        {

            using (var connection = new MySqlConnection(targetConnectionString))
            {
                // 参数验证
                if ((sqlCommands == null || sqlCommands.Count == 0) &&
                (dataList == null || dataList.Count == 0))
                {
                    Logger.Info("没有要执行的操作（SQL命令和数据列表均为空）");
                    return;
                }

                var tempFilePath = string.Empty;
                bool hasBulkInsert = dataList != null && dataList.Count > 0;

                // 创建临时文件（如果需要批量插入）
                if (hasBulkInsert)
                {
                    tempFilePath = Path.Combine(
                        Path.GetTempPath(),
                        $"bulk_gen_pt_insert_{Guid.NewGuid()}.csv");
                }


                await connection.OpenAsync();
                using (var transaction = connection.BeginTransaction())
                {
                    try
                    {

                        // 1. 写入CSV文件（如果有批量插入操作）
                        if (hasBulkInsert)
                        {
                            using (var writer = new StreamWriter(tempFilePath, false, Encoding.UTF8))
                            {
                                List<string> lclos = dataList[0].Keys.ToList();
                                string cols = string.Join(",", lclos);
                                await writer.WriteLineAsync(cols);

                                foreach (var row in dataList)
                                {

                                    var values = lclos.Select(column =>
                                    {
                                        if (!row.ContainsKey(column))
                                        {
                                            return string.Empty; // 其他列设为NULL
                                        }

                                        return EscapeCsvValue(row[column]);
                                    });
                                    await writer.WriteLineAsync(string.Join(",", values));
                                }
                            }
                        }
                        // 2. 执行批量插入（如果有数据）
                        if (hasBulkInsert)
                        {
                            List<string> lclos = dataList[0].Keys.ToList();
                            var bulkLoader = new MySqlBulkLoader(connection)
                            {
                                TableName = "device_elec_gen_pt_15min",
                                FileName = tempFilePath,
                                FieldTerminator = ",",
                                LineTerminator = "\n",
                                NumberOfLinesToSkip = 1,
                                CharacterSet = "utf8mb4",
                                Local = true,
                            };

                            bulkLoader.Columns.AddRange(lclos.Select(c => $"`{c}`"));
                            Logger.Info($"开始批量加载数据到 {bulkLoader.TableName}，记录数: {dataList.Count}");
                            var insertedCount = await Task.Run(() => bulkLoader.Load());
                            Logger.Info($"批量加载完成，成功插入 {insertedCount} 条记录");
                        }

                        // 3. 执行SQL命令
                        if (sqlCommands != null && sqlCommands.Count > 0)
                        {
                            Logger.Info($"准备执行 {sqlCommands.Count} 条SQL命令");

                            foreach (var sql in sqlCommands)
                            {
                                using (var command = new MySqlCommand(sql, connection, transaction))
                                {
                                    //Logger.Debug($"执行SQL: {sql}");
                                    int rowsAffected = await command.ExecuteNonQueryAsync();
                                }
                            }
                        }

                        // 4. 提交事务
                        transaction.Commit();
                        Logger.Info("所有批量操作已成功提交");
                    }
                    catch (Exception ex)
                    {
                        Logger.Error(ex, "执行批量操作时出错，正在回滚事务");
                        transaction.Rollback();
                        throw;
                    }
                    finally
                    {
                        connection.Close();
                        // 删除临时文件
                        try
                        {
                            if (!string.IsNullOrEmpty(tempFilePath) && File.Exists(tempFilePath))
                            {
                                File.Delete(tempFilePath);
                                Logger.Debug($"已删除临时文件: {tempFilePath}");
                            }
                        }
                        catch (Exception ex)
                        {
                            Logger.Warn(ex, $"删除临时文件失败: {tempFilePath}");
                        }
                    }
                }
            }
        }

        // 转义CSV值
        private static string EscapeCsvValue(object value)
        {
            // 处理 null 或 DBNull
            if (value == null || value == DBNull.Value)
                return string.Empty;



            // 特殊处理其他类型（如 decimal）
            if (value is decimal decimalValue)
                return decimalValue.ToString(CultureInfo.InvariantCulture);

            // 转换为字符串
            string text = value.ToString();

            // 检查是否需要引号
            bool needsQuotes =
                text.Contains(",") ||
                text.Contains("\"") ||
                text.Contains("\n") ||
                text.Contains("\r") ||
                text.StartsWith(" ") ||
                text.EndsWith(" ");

            if (needsQuotes)
            {
                // 转义双引号并包裹在双引号中
                return $"\"{text.Replace("\"", "\"\"")}\"";
            }

            return text;
        }


        #region SQL类型转换


        public static string ConvertListToStr(IEnumerable<string> fields)
        {
            if (fields != null && fields.Count() > 0)
            {
                return $@"'{string.Join("','", fields)}'";
            }
            else
            {
                return "NULL";
            }

        }


        public static string ConvertGuidToSql(string fields)
        {
            if (string.IsNullOrEmpty(fields))
            {
                return $@"NULL";
            }
            else
            {
                return $@"'{fields}'";
            }

        }

        public static string ConvertGuidToSelectSql(string fields)
        {
            if (string.IsNullOrEmpty(fields))
            {
                return $@"IS NULL";
            }
            else
            {
                return $@" = '{fields}'";
            }

        }

        public static string ConvertDecimalToSql(string fields)
        {
            if (string.IsNullOrEmpty(fields))
            {
                return $@"NULL";
            }
            else
            {
                return $@"{fields}";
            }

        }

        /// <summary>
        /// 按段分离数组
        /// </summary>
        /// <param name="inList">数组</param>
        /// <param name="splitNum">每段多少</param>
        /// <returns></returns>
        public static List<List<T>> GetSplitList<T>(List<T> inList, int splitNum)
        {
            List<List<T>> outList = new List<List<T>>();
            for (int i = 0; i < inList.Count; i = i + splitNum)
            {
                int n = i + splitNum;
                if (inList.Count < n)
                {
                    n = inList.Count;
                }
                List<T> numList = new List<T>();
                for (int m = i; m < n; m++)
                {
                    numList.Add(inList[m]);
                }
                outList.Add(numList);
            }
            return outList;
        }
        #endregion
    }
}
